Amazon Redshift: 列レベルのアクセスコントロールが出来るようになりました

#Amazon Redshift

#AWS

しんや

2020.03.08

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

先日、Amazon Redshiftにおいて「列レベルのアクセスコントロール」が出来るようになった旨、アナウンスがありました。

Amazon Redshift のための列レベルのアクセスコントロールの発表

Announcing column-level access control for Amazon Redshift

Amazon Redshift now supports access control at a column-level for data in Redshift. Customers can use column-level grant and revoke statements to help them meet their security and complianc... https://t.co/TJZlrbFvYI

— What’s New on AWS (@awswhatsnew) March 5, 2020

この機能は、クラスタのバージョンが「1.0.13059」以上となっていれば利用が可能です。

# SELECT VERSION();
                             version                                                          
---------------------------------------------------------------
 PostgreSQL 8.0.2 on i686-pc-linux-gnu, 
 compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3),
  Redshift 1.0.13448
(1 row)

当エントリでは、この機能について挙動等を見ていきたいと思います。

Usage Notes for Column-Level Access Control / GRANT - Amazon Redshift

構文

列レベルでのアクセスコントロールを行う際に用いるGRANT文の構文は以下の通り。

GRANT { { SELECT | UPDATE } ( column_name [, ...] ) [, ...] | ALL [ PRIVILEGES ] ( column_name [,...] ) } ON { [ TABLE ] table_name [, ...] } TO { username | GROUP group_name | PUBLIC } [, ...]

要点・注意事項

概要

基本的なポイントは以下の通り。

列レベルの権限を付与できるのはテーブルの所有者、またはスーパーユーザーのみ。
Amazon Redshiftのテーブルの場合、列レベルでSELECT及びUPDATEの権限を付与出来る。
Amazon Redshiftのビューに関しては、列レベルでSELECT権限を付与出来る。
テーブル内の全ての列に対するSELECT権限が無い場合、全ての列に対するSELECT操作の実行(SELECT *)は失敗する。
テーブルまたはビューに対するSELECTまたはUPDATEに関する権限がある状態で列を追加する場合、追加後も同様の権限、即ち(追加したカラムを含めて)全ての列に対する権限を保有している。
列レベル権限ではWITH GRANT OPTION句はサポートされていない。
遅延バインディングビューで列レベルの権限を付与する事は出来ない。

基本的な挙動の確認

検証用のスキーマ、及びテーブルを作成。合わせて参照用にデータを数件入れておきます。

# CREATE SCHEMA cac;
CREATE SCHEMA
# CREATE TABLE cac.column_access_control_test (
   user_id INT NOT NULL,
   user_name VARCHAR(30) NOT NULL,
   birth_day DATE NOT NULL,
   zipcode VARCHAR(20) NOT NULL,
   address VARCHAR(200) NOT NULL,
   join_date DATE NOT NULL,
   is_valid BOOLEAN NOT NULL,
   PRIMARY KEY(join_date, user_id)
);
CREATE TABLE

# INSERT INTO cac.column_access_control_test VALUES(1,'AAAAA','1990-01-01','1010000','AAAAAAAAAAAAAAAAA','2020-01-01',True);
# INSERT INTO cac.column_access_control_test VALUES(2,'BBBBB','1992-02-02','2220000','BBBBBBBBBBBBBBBBB','2020-02-02',True);
# INSERT INTO cac.column_access_control_test VALUES(3,'CCCCC','1993-03-03','3333333','CCCCCCCCCCCCCCCCC','2020-03-03',True);

# SELECT * FROM cac.column_access_control_test;
 user_id | user_name | birth_day  | zipcode |      address      | join_date  | is_valid 
---------+-----------+------------+---------+-------------------+------------+----------
       1 | AAAAA     | 1990-01-01 | 1010000 | AAAAAAAAAAAAAAAAA | 2020-01-01 | t
       2 | BBBBB     | 1992-02-02 | 2220000 | BBBBBBBBBBBBBBBBB | 2020-02-02 | t
       3 | CCCCC     | 1993-03-03 | 3333333 | CCCCCCCCCCCCCCCCC | 2020-03-03 | t
(3 rows)

アクセス検証に用いるユーザーを別途作成。このユーザーに対して任意のスキーマの利用権限、及び上記作成テーブルの任意のカラム数個(user_id, user_name, join_date)にのみSELECT権限を付与しておきました。

# CREATE USER cactestuser PASSWORD '****************';
CREATE USER
# GRANT USAGE ON SCHEMA cac TO cactestuser;
GRANT
# GRANT SELECT(user_id, user_name, join_date) ON cac.column_access_control_test TO cactestuser;
GRANT

上記作成したユーザーで別途ログイン後、対象テーブルに対してSELECT文を実行。許可を得ているカラムに関してはSELECT文を実行出来、そうでないカラムが入っている場合は権限設定によりSELECT文が実行出来ないことが確認出来ました。

% psql -h xxxxxxxxxxxxxxxxxx -U cactestuser -d yyyyyyyyyy -p ####
Password for user cactestuser: 
psql (12.1, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

=>
=> SELECT * FROM cac.column_access_control_test;  /** テーブルカラム全項目に対してクエリ実施. */
ERROR:  permission denied for relation column_access_control_test
=> 
=> SELECT
  user_id, 
  user_name, 
  join_date, 
  birthday  /** 許可を与えられていないカラムを別途指定. */
FROM
  cac.column_access_control_test;
ERROR:  column "birthday" does not exist in column_access_control_test

 => SELECT user_id, user_name, join_date FROM cac.column_access_control_test;
 user_id | user_name | join_date  
---------+-----------+------------
       1 | AAAAA     | 2020-01-01
       2 | BBBBB     | 2020-02-02
       3 | CCCCC     | 2020-03-03
(3 rows)

テーブルレベル・列レベルの権限の兼ね合いについて

テーブルレベルと列レベルの両方で同じ特権を保持する事は出来ません。ユーザー：data_scientistはテーブル：employeeのSELECT権限とカラムemployee.departmentのSELECT権限を両方持つことは出来ない、という形となります。

テーブルとテーブル内の列に同じ権限を付与する場合は以下の内容を考慮しておく必要があります。

ユーザーがテーブルに対しての権限を持っている場合、列レベルで同じ権限を付与しても効果はない。
ユーザーがテーブルに対しての権限を持っている場合、テーブルの1以上の列に対して同じ権限を取り消すとエラーが返される。代わりに、テーブルレベルで特権を取り消す。
ユーザーが列レベルでの権限を持っている場合、テーブルレベルで同じ権限を付与するとエラーが返される。
ユーザーが列レベルでの権限を持っている場合、テーブルレベルで同じ権限を取り消すと、テーブルの全ての列・テーブル双方の権限が取り消される。

マテリアライズド・ビューについて

マテリアライズド・ビューを作成するには、ベーステーブルに対するテーブルレベルのSELECT権限が必要となります。

特定のテーブル列に対して列レベルの権限を持っている場合でも、それらの列のみを使ったマテリアライズド・ビューは作れません。ただし、通常ビューと同様、マテリアライズド・ビューの列にSELECT権限は付与可能です。

参照情報

列レベルの権限の付与に関する情報については『PG_ATTRIBUTE_INFO』ビューを使うことで入手が可能です。

...とあるんですが、これだけだとさっぱり何をどうやれば欲しい情報が取れるのかが分からないw

一応、該当テーブル(PG_ATTRIBUTE_INFO)のカラム:attaclに値が入っているもので絞り込む事は出来ましたが、ここから「該当ユーザーは、どのテーブルのどのカラムに対してどういう権限を持っているのか」を割り出して情報として整理し、確認に利用したいところです。出来るのかな...一旦当エントリではここまでにしておきたいと思います。

# SELECT * FROM PG_ATTRIBUTE_INFO WHERE attacl IS NOT NULL;
 attrelid |  attname  | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount | attisdistkey | attispreloaded | attsortkeyord | attencodingtype | attencrypttype |        attacl        
----------+-----------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+--------------+------------+-------------+--------------+----------------+---------------+-----------------+----------------+----------------------
  2148318 | user_id   |       23 |            -1 |      4 |      1 |        0 |          -1 |        -1 | t        | p          | i        | t          | f         | f            | t          |           0 | f            | f              |             0 |             148 |              0 | {cactestuser=r/superuserxxx}
  2148318 | user_name |     1043 |            -1 |     -1 |      2 |        0 |          -1 |        34 | f        | x          | i        | t          | f         | f            | t          |           0 | f            | f              |             0 |             131 |              0 | {cactestuser=r/superuserxxx}
  2148318 | join_date |     1082 |            -1 |      4 |      6 |        0 |          -1 |        -1 | t        | p          | i        | t          | f         | f            | t          |           0 | f            | f              |             0 |             148 |              0 | {cactestuser=r/superuserxxx}
(3 rows)

まとめ

という訳で、Amazon Redshiftにおける「列レベルのアクセスコントロール」に関する内容の紹介でした。

より細やかな情報の制御が出来る事は便利になった一方で、「今どういう制御状態にあるのか」という情報も合わせて参照、利用出来るようになっていると使い勝手も良くなるのかなぁ、と思った次第です。(この辺、PG_ATTRIBUTE_INFOで良い感じで取れるかなーと思ったけど軽く心が折れたw ので今後誰かが超絶便利なSQLかプログラム的なものを編み出してくれることを期待)